<?php

/**
 * Description de la classe projet
 */
class Project {
    
    // constante globale à l'application WSK PORTAL
    const USER = 0;
    const ADMIN = 1;

    const GRAPHE_COURBE = 1;
    const GRAPHE_POINT = 2;
    const GRAPHE_HIST0 = 3;
    const GRAPHE_SECTEUR = 4;
    
    // les colonnes par défaut pour tous les projets
    const STATION_ID_COLUMN = 'station_id';
    const DATE_COLUMN = 'dat';

    private $id;
    private $project_name;
    private $table_name;
    private $columns;
    private $image;
    private $graphe_type;
    private $user_type;
    private $nb_lign;
    /**
     *
     * @param type $id
     * @param type $project_name
     * @param type $table_name
     * @param type $columns
     * @param type $image
     * @param type $graphe_type 
     * @param type $user_type
     * @param type $default_nb_points
     */
    public function __construct($id, $project_name, $table_name, $columns, $image, $graphe_type, $user_type,$default_nb_points) {
        $this->id = $id;
        $this->project_name = $project_name;
        $this->table_name = $table_name;
        $this->columns = $columns;
        $this->image = $image;
        $this->graphe_type = $graphe_type;
        $this->user_type = $user_type;
        $this->nb_lign = $default_nb_points;
    }

    /**
     * Getter Project id
     * @return type int
     */
    public function getId() {
        return $this->id;
    }

    /**
     * Getter Project name
     * @return type string
     */
    public function getProjectName() {
        return $this->project_name;
    }

    /**
     * Getter Project table name
     * @return type int
     */
    public function getTableName() {
        return $this->table_name;
    }

    /**
     * Getter Project colums
     * @return type int
     */
    public function getColumns() {
        return $this->columns;
    }

    /**
     * Getter Project image
     * @return type int
     */
    public function getImage() {
        return $this->image;
    }

    /**
     * Getter Project graphe type
     * @return type int
     */
    public function getGrapheType() {
        return $this->graphe_type;
    }

    /**
     * Getter Project user type
     * @return type int
     */
    public function getUserType() {
        return $this->user_type;
    }
    /**
     * Getter Project user type
     * @return type varchar
     */
    public function   getProjectDefaultNBPoints() {
        return $this->nb_lign;
    }
    /**
     * Sauvegarde un projet en base de données
     */
    public function save() {
    	Log::createLog("INSERT INTO `wsk_projects` (`id_project`, `project_name`, `table_name`, `image_file_name`, `default_graphe_type`,`default_nb_points`, `date_created`, `date_updated`, `date_deleted`) VALUES (NULL, '$this->project_name', '$this->table_name', NULL, " . $this->graphe_type . ", ".$this->nb_lign.",CURRENT_TIMESTAMP, NULL, NULL);");
    	 
        Sql::exec("INSERT INTO `wsk_projects` (`id_project`, `project_name`, `table_name`, `image_file_name`, `default_graphe_type`,`default_nb_points`, `date_created`, `date_updated`, `date_deleted`) VALUES (NULL, '$this->project_name', 'wsk_$this->table_name', NULL, " . $this->graphe_type . ", ".$this->nb_lign.",CURRENT_TIMESTAMP, NULL, NULL);");
    }

    /**
     * Sauvegarde des utilisateurs et des modérateurs d'un projet
     * @param type $admins
     * @param type $users
     */
    public function saveProjectUserAdmin($admins, $users) {
        
        foreach ($admins as $admin)
            Sql::exec("INSERT INTO `wsk_members_has_projects` (`id_member`, `id_project`, `user_type`, `date_created`, `date_updated`, `date_deleted`) VALUES ('" . $admin . "', '" . $this->id . "','" . Project::ADMIN . "', CURRENT_TIMESTAMP, NULL, NULL);");

        foreach ($users as $user)
            Sql::exec("INSERT INTO `wsk_members_has_projects` (`id_member`, `id_project`, `user_type`, `date_created`, `date_updated`, `date_deleted`) VALUES ('" . $user . "', '" . $this->id . "', '" . Project::USER . "', CURRENT_TIMESTAMP, NULL, NULL);");
    }
    
    /**
     * Suppression des utilisateurs et / ou modérateurs d'un projet
     */
    public function deleteProjectUserAdmin() {
        Sql::exec("DELETE FROM `wsk_members_has_projects` WHERE `wsk_members_has_projects`.`id_project` = ".$this->id.";");
        //Log::createLog("delete : " + "DELETE FROM `members_has_projects` WHERE `members_has_projects`.`id_project` = ".$this->id.";");
    }

    /**
     * Mise à jour d'un projet
     */
    public function update() {
        $sql = "UPDATE `wsk_projects` SET  `project_name` =  '".$this->project_name."', `default_graphe_type` =  '".$this->graphe_type."',`default_nb_points` =  '".$this->nb_lign."', `date_updated` =  NOW() WHERE  `id_project` =$this->id;";
        Sql::exec($sql);
    }

    /**
     * Suppression d'un projet
     * !! suppression logique !!
     */
    public function delete() {
        Sql::exec("UPDATE  `wsk_projects` SET  `date_deleted` = NOW() WHERE  `id_project` =". $this->id .";");
    }
    
    /**
     * Suppression des colonnes d'un projet
     * !!! les données seront donc supprimées également !!!
     * @param type $columnsToDelete
     */
    public function deleteColumns($columnsToDelete){
        Log::createLog("Project::deleteColumns - START");
        foreach($columnsToDelete as $c){
            Log::createLog("Project::deleteColumns - foreach start");
            if($c != ""){
                Sql::exec ("UPDATE  `wsk_columns` SET  `date_deleted` = NOW() WHERE  `id_project` =". $this->id ." AND `column_name` ='$c';");
                Sql::exec("ALTER TABLE `wsk_$this->table_name` DROP `$c`");
            }
        }
        Log::createLog("Project::deleteColumns - END");
    }

    /**
     * Récupération des administrateurs d'un projet
     * @return type
     */
    public function getAdminProject() {

        $req = mysql_query("select wsk_projects.id_project, login, user_type, wsk_members.id_member from wsk_projects, wsk_members_has_projects, wsk_members where wsk_projects.id_project = " . $this->id . " and wsk_members_has_projects.id_project = wsk_projects.id_project and user_type = 1 and wsk_members.id_member = wsk_members_has_projects.id_member");
        $list = array();
        while ($row = mysql_fetch_array($req)) {
            $list[] = $row["login"];
        }

        mysql_free_result($req);
        return $list;
    }
    
    /**
     * Récupération des utilisateurs d'un projet
     * @return type
     */
    public function getUserProject() {
    	$req = mysql_query("select wsk_projects.id_project,login,user_type,wsk_members.id_member from wsk_projects,wsk_members_has_projects,wsk_members where wsk_projects.id_project = " . $this->id . " and wsk_members_has_projects.id_project = wsk_projects.id_project and user_type = ".Member::USER." and wsk_members.id_member = wsk_members_has_projects.id_member");
    	$list = array();
    	while ($row = mysql_fetch_array($req)) {
    		$list[] = $row["login"];
    	}
    
    	mysql_free_result($req);
    	return $list;
    }


    /**
     * Retourne si l'utilisateur est un administrateur
     * @param type $idMember
     * @return type
     */
    public function isAdmin($idMember){
        $sql = "SELECT * FROM  `wsk_members_has_projects` WHERE  `id_member` =".$idMember." AND `id_project` =".$this->id." AND `user_type` =1";
        
        return Sql::num_rows(Sql::exec($sql));
    }
    
    /**
     * Retourne si l'utilisateur est un mebre du projet
     * @param type $idMember
     * @return type
     */
    public function isMember($idMember){
        $sql = "SELECT * FROM  `wsk_members_has_projects` WHERE  `id_member` =".$idMember." AND `id_project` =".$this->id." AND `user_type` =0";
        //Log::createLog("\nisMember : " .Sql::num_rows(Sql::exec($sql)) );
        return Sql::num_rows(Sql::exec($sql));
    }

    // STATIC METHODS

    /**
     * Récupération de tous les projets existant
     * @return List of Projects
     */
    public static function getProjects() {

        $list = Array();

        $req = mysql_query("SELECT * FROM wsk_projects WHERE `date_deleted` IS NULL ");
        while ($row = mysql_fetch_array($req)) {
            $list[] = new Project($row["id_project"], $row["project_name"], $row["table_name"], Column::getColumnsProject($row["id_project"]), $row["image_file_name"], $row["default_graphe_type"], null,$row["default_nb_points"]);
        }

        mysql_free_result($req);
        return $list;
    }

    /**
     * Retourne une liste de tous les utilisateurs d'un projet
     * @param int $id_member
     */
    public static function getProjectsOfMember($id_member, $statut_member) {
        if ($statut_member == Member::FULLADMIN)
            return Project::getProjects();
        else {
            $list = Array();

            $req = mysql_query("SELECT wsk_projects.id_project, wsk_projects.project_name, wsk_projects.table_name, wsk_projects.image_file_name, wsk_projects.default_graphe_type, wsk_members_has_projects.user_type, wsk_projects.default_nb_points FROM wsk_projects 
				JOIN wsk_members_has_projects ON wsk_members_has_projects.id_project=wsk_projects.id_project
				WHERE wsk_members_has_projects.id_member=" . $id_member);

            while ($row = mysql_fetch_array($req)) {
                $list[] = new Project($row["id_project"], $row["project_name"], $row["table_name"], Column::getColumnsProject($row["id_project"]), $row["image_file_name"], $row["default_graphe_type"], $row["user_type"],$row["default_nb_points"]);
            }

            mysql_free_result($req);
            return $list;
        }
    }

    /**
     * Récupère un projet par son nom
     * @param type $project_name
     * @return the project
     */
    public static function getProjectByName($project_name) {
        $req = mysql_query("SELECT * FROM wsk_projects WHERE project_name='" . $project_name . "'");
        $row = mysql_fetch_array($req);
        mysql_free_result($req);

        return new Project($row["id_project"], $row["project_name"], $row["table_name"], Column::getColumnsProject($row["id_project"]), $row["image_file_name"], $row["default_graphe_type"], null,$row["default_nb_points"]);
    }

    /**
     * Récupère un projet depuis son ID unique
     * @param type $project_name
     * @return the project
     */
    public static function getProjectByID($id_project) {
        $req = mysql_query("SELECT * FROM wsk_projects WHERE id_project='" . $id_project . "'");
        $row = mysql_fetch_array($req);
        mysql_free_result($req);

        return new Project($row["id_project"], $row["project_name"], $row["table_name"], Column::getColumnsProject($row["id_project"]), $row["image_file_name"], $row["default_graphe_type"], null,$row["default_nb_points"]);
    }

    /**
     * Récupère les données d'un projet
     * @param type $project_id
     * @param type $cards
     * @param type $column
     * @param type $include_services_data
     * @return type
     */
    public static function getProjectTableContent($project_id, $cards, $column, $include_services_data) {
    	$list = array();
    	
	    if(!empty($column))
	    {
		    $req = mysql_query("SELECT default_nb_points, table_name FROM wsk_projects WHERE id_project = ". $project_id);
		    $result = mysql_fetch_assoc($req);
		    
		    foreach(explode(',', $cards[0]) as $card)
		    {
		       	$req = mysql_query("SELECT ".self::DATE_COLUMN.", ".self::STATION_ID_COLUMN.", " . $column . " FROM " . $result['table_name'] . " WHERE ".self::STATION_ID_COLUMN." = ".$card." ORDER BY ".self::DATE_COLUMN." DESC LIMIT ". $result['default_nb_points']);
		   	   	
				while ($row = mysql_fetch_assoc($req)) {
					$row[self::DATE_COLUMN] = trim($row[self::DATE_COLUMN]);
					
					if(strlen(trim($row[self::DATE_COLUMN])) == 13) {
		           		$row[self::DATE_COLUMN] = '0'.$row[self::DATE_COLUMN];
		           	}
					           
					$date = DateTime::createFromFormat('dmYHis', $row[self::DATE_COLUMN]);
		          	$list[$row[self::STATION_ID_COLUMN]][] = array($date->format('d M Y H:i'), $row[$column]);
				}
			
				mysql_free_result($req);
		    }
	    }
    	
    	if($include_services_data == 'true') {
    		$services_data = mysql_query("SELECT id_services_data, date, ".self::STATION_ID_COLUMN." FROM wsk_services_data WHERE id_project = $project_id ORDER BY id_services_data DESC LIMIT 10");
    		$i = 0;
    		while ($row = mysql_fetch_assoc($services_data)) {
	            foreach($row as $k => $v) {
	            	if($k != 'date') {
	            		$list['services_data']['data_'.$i][] = array($k, $v);
	            	}
            		else {
            			// Si le numéro du jour ne commence pas par un 0
            			if(strlen($v) == 13) {
            				$v = '0'.$v;
            			}
            			
		            	$date = DateTime::createFromFormat('dmYHis', $v);
	            		$list['services_data']['data_'.$i][] = array($k, $date->format('d M Y H:i'));
            		}
	           	}
	           	
	           	$i++;
    		}
    		
    		mysql_free_result($services_data);
        }
        
        return $list;
    }

    /**
     * Récupère les données d'un projet par rapport à une date de début et de fin
     * @param type $project_id
     * @param type $cards
     * @param type $column
     * @param type $start
     * @param type $end
     * @param type $include_services_data
     * @return type
     */
    public static function getProjectTableContentFromDate($project_id, $cards, $columns, $start, $end, $include_services_data) {
    	$list = array();
    	
    	$start = DateTime::createFromFormat('dmYHis', $start);
	   	$end = DateTime::createFromFormat('dmYHis', $end);
    	
    	if(!empty($columns[0]))
    	{
			$req = mysql_query("SELECT default_nb_points, table_name FROM wsk_projects WHERE id_project = ". $project_id);
	    	$result = mysql_fetch_assoc($req);
		   	$req = mysql_query("SELECT " . implode(",", $columns) .", ".self::DATE_COLUMN.", ".self::STATION_ID_COLUMN." FROM " . $result['table_name'] . " WHERE ".self::STATION_ID_COLUMN." IN (" . implode(',', $cards) . ") ORDER BY ".self::DATE_COLUMN." DESC");

		   	while ($row = mysql_fetch_assoc($req)) {
		        $row[self::DATE_COLUMN] = trim($row[self::DATE_COLUMN]);
		        // Si le numéro du jour ne commence pas par un 0
		        if(strlen($row[self::DATE_COLUMN]) == 13) {
		            $row[self::DATE_COLUMN] = '0'.$row[self::DATE_COLUMN];
		        }
		            			
		        $date = DateTime::createFromFormat('dmYHis', $row[self::DATE_COLUMN]);
		            
		        if (($date >= $start) && ($date <= $end))
		        {
		        	foreach(explode(',', $columns[0]) as $k => $column)
	        		{
		                $list[$row[self::STATION_ID_COLUMN]][$column][] = array($date->format('d M Y H:i'), $row[$column]);
	        		}
		        }
		    }
		    
        	mysql_free_result($req);
    	}
    	
    	if($include_services_data == 'true') {
    		$services_data = mysql_query("SELECT id_services_data, date, ".self::STATION_ID_COLUMN." FROM wsk_services_data WHERE id_project = $project_id ORDER BY id_services_data DESC LIMIT 10");
    		$i = 0;
    		while ($row = mysql_fetch_assoc($services_data)) {
    			$i++;
	            foreach($row as $k => $v) {
	            	$row['date'] = trim($row['date']);
            		// Si le numéro du jour ne commence pas par un 0
	            	if(strlen($row['date']) == 13) {
            			$row['date'] = '0'.$row['date'];
            		}
            		
	            	$date = DateTime::createFromFormat('dmYHis', $row['date']);
	            	
	            	if ($date >= $start && $date <= $end)
	            	{
		            	if($k != 'date')
		            	{
		            		if($k != 'station_id')
		            			$list['services_data'][$row[self::STATION_ID_COLUMN].'_'.$i][] = array($k, $v);
		            	}
	            		else
	            		{
		            		if(strlen($row['date']) == 13) {
		            			$v = '0'.$v;
		            		}
		            		
		            		$v = DateTime::createFromFormat('dmYHis', $v);
	            			$list['services_data'][$row[self::STATION_ID_COLUMN].'_'.$i][] = array($k, $v->format('d M Y H:i'));
	            		}
	            	}
	           	}
    		}
    		
    		mysql_free_result($services_data);
        }
        
        return $list;
    }

    /**
     * Récupère les différentes cartes WSK d'un projet
     * @param type $project_name
     * @return type
     */
    public static function getProjectCards($project_name) {
        $req = mysql_query("SELECT ".self::STATION_ID_COLUMN." FROM " . $project_name . " GROUP BY ".self::STATION_ID_COLUMN);
        $list = array();

        while ($row = mysql_fetch_array($req)) {
            $list[] = $row[self::STATION_ID_COLUMN];
        }

        mysql_free_result($req);

        return $list;
    }

    /**
     * Récupère un fichier CSV par son nom
     * @param type $file_name
     * @return \Project
     */
    public static function getCSVByName($file_name) {
        $req = mysql_query("SELECT * FROM wsk_projects WHERE file_name = '" . $file_name . "'");

        while ($row = mysql_fetch_array($req)) {
            $project = new Project($row["id_project"], $row["project_name"], $row["table_name"], Column::getColumnsProject($row["id_project"]), $row["image_file_name"], $row["default_graphe_type"], null,$row["default_nb_points"]);
        }

        return $project;
    }

    /**
     * Création de la table associée au projet
     * @param type $columns
     * @param type $columnsName
     */
    public function createTableProject($columns, $columnsName) {
        Log::createLog("Project::CreateTableProject : start");
        $sql = "CREATE TABLE IF NOT EXISTS `" . $this->table_name . "` (`id` int(11) NOT NULL AUTO_INCREMENT,";

        foreach ($columns as $column) {
            Log::createLog("Project::CreateTableProject : c ".$column);
            if($column != "")
                $sql .= "   `" . $column . "` varchar(50) COLLATE utf8_unicode_ci NOT NULL, ";
        }

        $sql .= "  PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;";
        Log::createLog($sql . "\n" . " à " . date("d/m/Y H:i:s", time()) . " \n");

        Sql::exec($sql);

        $sql = "INSERT INTO  `wsk_columns` (  `id_column` ,  `id_project` ,  `column_name` ,  `column_usual_name` ,  `order` ,  `date_created` ,  `date_updated` ,  `date_deleted` ) VALUES ";

        // $i = 2 pour ne pas enregistrer les deux premières colonnes station_id et dat
        for ($i = 2; $i < count($columns)-1; $i++) {
            $sql .= " (NULL ,  '" . $this->id . "',  '" . $columns[$i] . "',  '" . $columnsName[$i] . "',  '" . ($i + 1) . "', CURRENT_TIMESTAMP , NULL , NULL)"  ;
            $sql .= ($i == count($columns)-2) ? ";" : ",";  
        }
        
        Log::createLog("requête : $sql Ligne n° $i insérée \n" . " à " . date("d/m/Y H:i:s", time()) . " \n");
        Sql::exec($sql);
        Log::createLog("Project::CreateTableProject : end");

    }
    
    /**
     * Mets à jour la table d'un projet
     * @param type $columns
     * @param type $columnsName
     */
    public function updateTableProject($columns, $columnsName) {
        Log::createLog("Project::updateTableProject - start");
        $addColumn = false;
        
        foreach ($columns as $column) {
            if($column != ""){
                $sql = mysql_query("SELECT $column FROM $this->table_name;");
                Log::createLog("query : ".$sql);
                if($sql == 0){
                    Log::createLog("ALTER TABLE  `$this->table_name` ADD  `$column` VARCHAR( 50 ) NOT NULL");
                    Sql::exec("ALTER TABLE  `$this->table_name` ADD  `$column` VARCHAR( 50 ) NOT NULL");
                    
                }
            }
        }

        $query = "INSERT INTO  `wsk_columns` (  `id_column` ,  `id_project` ,  `column_name` ,  `column_usual_name` ,  `order` ,  `date_created` ,  `date_updated` ,  `date_deleted` ) VALUES ";
        
        
        // $i = 2 pour ne pas enregistrer les deux premières colonnes station_id et dat
        for ($i = 2; $i < count($columns)-1; $i++) {
            //$sql = mysql_query("SELECT $columns[$i] FROM $this->table_name;");
            $sql = mysql_query("SELECT *
                                FROM  `wsk_columns` 
                                WHERE  `column_name` =  '$columns[$i]'
                                AND  `date_deleted` IS NULL AND `id_project`=$this->id");

            $rows = mysql_num_rows($sql);
            Log::createLog("query update: ".$rows);
            if($rows == 0){
                $addColumn = true;
                $query .= " (NULL ,  '" . $this->id . "',  '" . $columns[$i] . "',  '" . $columnsName[$i] . "',  '" . ($i + 1) . "', CURRENT_TIMESTAMP , NULL , NULL)"  ;
                $query .= ($i == count($columns)-2) ? ";" : ",";
            }
        }
        
        Log::createLog("requête : $query Ligne n° $i insérée \n" . " le " . date("d/m/Y H:i:s", time()));
        if($addColumn)
            Sql::exec($query);
        Log::createLog("Project::updateTableProject : end");
        
       // Log::createLog($sql . "\n" . " à " . date("d/m/Y H:i:s", time()) . " \n");
    }
    
    /**
     * Vérifie la disponibilité d'un nom de projet
     * @param type $name
     * @return int
     */
    public static function checkAvailability($name) {
        $result = mysql_query('SELECT table_name FROM wsk_projects WHERE table_name = "' . $name . '"');
        Log::createLog("Project::checkAvailability - start");
        
        Log::createLog("Project::checkAvailability - " . 'SELECT table_name FROM wsk_projects WHERE table_name = "' . $name . '"');
        //if number of rows fields is bigger them 0 that means it's NOT available '  
        if (mysql_num_rows($result) > 0) {
            Log::createLog("Project::checkAvailability - 0");
            //and we send 0 to the ajax request  
            return 0;
        } else {
            Log::createLog("Project::checkAvailability - 1");
            //else if it's not bigger then 0, then it's available '  
            //and we send 1 to the ajax request  
            return 1;
        }
    }

}

?>